import os
import sys
import pandas as pd
from datetime import date
# print("[调试]1 sys.path:", sys.path)

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))
# print("[调试]2 sys.path:", sys.path)
# sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from server import server, db
from models import Product

# 统计金额区间
PRICE_BINS = [
    (0, 1, '0-1万'),
    (1, 5, '1-5万'),
    (5, 10, '5-10万'),
    (10, 50, '10-50万'),
    (50, 100, '50-100万'),
    (100, float('inf'), '100万以上')
]

def get_price_range(price):
    # Ensure price is a number, default to 0 if None or invalid
    try:
        price_val = float(price) if price is not None else 0
    except (ValueError, TypeError):
        price_val = 0

    for low, high, label in PRICE_BINS:
        if low <= price_val < high:
            return label
    return '未知' # Should not happen with float('inf') but good practice

def generate_stats():
    today_str = date.today().strftime("%Y-%m-%d")
    with server.app_context():
        orgs = db.session.query(Product.org_short_name_c).filter(Product.is_active==True).distinct()
        orgs = [o[0] for o in orgs if o[0]]

        for org in orgs:
            print(f"正在处理机构: {org}", flush=True) # <-- 添加 flush=True
            devices = Product.query.filter(
                Product.org_short_name_c==org,
                Product.is_active==1
            ).all()
            if not devices:
                print(f"机构 {org} 没有找到活跃设备，跳过。", flush=True) # <-- 添加 flush=True
                continue

            # Create DataFrame with necessary columns
            df = pd.DataFrame([{
                "设备分类": d.equipment_category if d.equipment_category else '未知分类',
                "价格": d.price or 0
            } for d in devices])

            # Add price range column
            df['金额区间'] = df['价格'].apply(get_price_range)

            # Group by category and price range, calculate count and sum
            # Use 'size' for count to include groups with 0 items if needed, but agg(['count', 'sum']) is fine here
            grouped = df.groupby(["设备分类", "金额区间"])["价格"].agg(['count', 'sum']).reset_index()
            grouped.rename(columns={'count': '台数', 'sum': '金额'}, inplace=True)

            # Pivot the table to get price ranges as columns with '台数' and '金额' sub-columns
            pivot_df = grouped.pivot_table(
                index='设备分类',
                columns='金额区间',
                values=['台数', '金额'], # Pivot both count and sum
                fill_value=0
            )

            # Ensure all price range columns are present, even if empty for a category
            # This creates multi-level columns like ('0-1万', '台数'), ('0-1万', '金额'), ...
            all_price_ranges = [label for _, _, label in PRICE_BINS]
            # Create expected multi-level columns
            expected_cols = pd.MultiIndex.from_product([['台数', '金额'], all_price_ranges], names=['Metric', 'Range'])
            # Reindex and sort columns to ensure consistent order
            pivot_df = pivot_df.reindex(columns=expected_cols, fill_value=0).sort_index(axis=1)

            # Flatten the multi-level columns for easier handling and renaming
            # New column names will be like '台数_0-1万', '金额_0-1万', etc.
            pivot_df.columns = [f'{col[1]}_{col[0]}' for col in pivot_df.columns]

            # Calculate total count and total amount per category
            category_totals = df.groupby("设备分类")["价格"].agg(['count', 'sum']).reset_index()
            category_totals.rename(columns={'count': '合计台数', 'sum': '合计金额(万元)'}, inplace=True)

            # Merge pivoted data with category totals
            final_df = pivot_df.merge(category_totals, on='设备分类', how='left')

            # Add '日期' column
            final_df['日期'] = today_str

            # Reorder columns to match the image: 设备分类, 日期, 0-1万_台数, 0-1万_金额, ..., 100万以上_台数, 100万以上_金额, 合计台数, 合计金额(万元)
            # Generate the list of price range columns in the desired order
            price_range_cols_ordered = []
            for pr_range in all_price_ranges:
                price_range_cols_ordered.append(f'{pr_range}_台数')
                price_range_cols_ordered.append(f'{pr_range}_金额')

            ordered_columns = ['设备分类', '日期'] + price_range_cols_ordered + ['合计台数', '合计金额(万元)']
            final_df = final_df[ordered_columns]

            # Add total row
            total_row_data = {}
            for col in ordered_columns:
                if col in ['合计台数', '合计金额(万元)'] or col.endswith('_台数') or col.endswith('_金额'):
                     # Sum numeric columns, handle potential non-numeric types gracefully
                    try:
                        total_row_data[col] = final_df[col].sum()
                    except TypeError:
                        total_row_data[col] = 0 # Default to 0 if summing fails
                elif col == '设备分类':
                    total_row_data[col] = '合计数据' # Use '合计数据' as in the image
                elif col == '日期':
                    total_row_data[col] = today_str # Add date to total row as in the image
                else:
                    total_row_data[col] = '' # Other columns can be empty or a placeholder

            total_df = pd.DataFrame([total_row_data])

            # Combine category data and total row
            final_df = pd.concat([final_df, total_df], ignore_index=True)

            # Save to excel
            outdir = os.path.dirname(__file__)
            print(f"开始统计outdir {outdir}", flush=True) # <-- 添加 flush=True
            # Use a consistent filename format, maybe including the date if needed, but sticking to org name for now
            outfile = os.path.join(outdir, 'xlsx',f"{org}_设备分类及价格区间统计.xlsx")

            # Check if directory exists, create if not
            os.makedirs(os.path.dirname(outfile), exist_ok=True)


            if os.path.exists(outfile):
                try:
                    # Read existing data
                    old_df = pd.read_excel(outfile)
                    # Remove existing data for today's date
                    old_df = old_df[old_df['日期'] != today_str]
                    # Concatenate old data with new data
                    final_df = pd.concat([old_df, final_df], ignore_index=True)
                except Exception as e:
                    print(f"Error reading existing Excel file {outfile}: {e}. Overwriting.", flush=True) # <-- 添加 flush=True
                    # If reading fails, just overwrite

            # Save the combined data
            print(f"尝试写入文件: {outfile}", flush=True) # <-- 添加 flush=True
            try:
                with pd.ExcelWriter(outfile, mode='w') as writer:
                    final_df.to_excel(writer, sheet_name='设备分类及价格区间统计', index=False)
                print(f"文件写入成功: {outfile}", flush=True) # <-- 添加 flush=True
            except Exception as e:
                print(f"文件写入失败: {outfile}, 错误: {e}", flush=True) # <-- 添加 flush=True

            print(f"{org} 统计完成，文件：{outfile}", flush=True) # <-- 添加 flush=True

if __name__ == "__main__":
    print("开始统计各医院excel")
    generate_stats()
    print("统计各医院excel结束")